IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetSystemListByUserPrivilege')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GetSystemListByUserPrivilege
GO

/****** Object:  StoredProcedure [dbo].[USP_GetSystemListByUserPrivilege]    Script Date: 10/22/2010 13:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Boonyarit
-- Create date: 21/10/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetSystemListByUserPrivilege]
	-- Add the parameters for the stored procedure here
	@staff_code int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	declare @group_user_code as int
	declare @department_code as int
	declare @auth_sub_menu_table as table(auth_department_code int, department_code int, sub_menu_code int)

	select @group_user_code = group_user_code, @department_code = department_code from staff where staff_code = @staff_code

	/*************************************************/
	/*  Get sub menu by user's privilege             */
	/*                                               */
	/*                                               */
	/*************************************************/
	if exists (select * from stp_auth_staff where staff_code = @staff_code and auth_staff_status = 1) 
		insert into @auth_sub_menu_table (auth_department_code, department_code, sub_menu_code)
		select auth_department_code, department_code, sub_menu_code from stp_auth_department 
		where auth_department_code in (
			select auth_dep_code from stp_auth_group 
			where auth_group_code in (
				select auth_dep_code from stp_auth_staff where staff_code = @staff_code and auth_staff_status = 1
			) and group_code = @group_user_code and auth_group_status = 1
		)
	else if exists (select auth_dep_code from stp_auth_group where group_code = @group_user_code and auth_group_status = 1)
			insert into @auth_sub_menu_table (auth_department_code, department_code, sub_menu_code)
			select auth_department_code, department_code, sub_menu_code from stp_auth_department 
			where auth_department_code in (
				select auth_dep_code from stp_auth_group 
				where group_code = @group_user_code and auth_group_status = 1
			)
	else 
			insert into @auth_sub_menu_table (auth_department_code, department_code, sub_menu_code)
			select auth_department_code, department_code, sub_menu_code from stp_auth_department 
			where department_code = @department_code and auth_department_status = 1


	/*************************************************/
	/*  Get list of menu from @auth_sub_menu_table   */
	/*                                               */
	/*                                               */
	/*************************************************/
	-- Menu
	select main.main_system_code main_system_code,
			main.main_system_name main_system_name,
			main.main_system_picture main_system_picture
	from stp_main_system main 
	where main.main_system_status = 1
			and main.main_system_type > 0
			and main.main_system_code in (
					select menu.main_system_code
					from stp_menu_system menu
					where menu.menu_system_status = 1
							and menu.menu_system_code in (
									select sub_menu.menu_system_code
									from stp_sub_menu sub_menu
									where sub_menu.sub_menu_status = 1
											and sub_menu.sub_menu_code in (select sub_menu_code from @auth_sub_menu_table)
									)
					)
	
END


GO


